﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SQLite;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
namespace SQLite
{
    public class SQLite
    {
        protected static string mFileName = System.Environment.UserName+".sqlite";
        private SQLiteConnection mConnection;
        public static bool SQLiteCreateDB(string filename)
        {
            bool a = false;
            if (!File.Exists(filename))
            {
                System.Data.SQLite.SQLiteConnection.CreateFile(filename);
                a = true;
            }
            else
            {
                 a = true;

            }
            return a;
        }
        public SQLite(string connectionString)
        {
            if (File.Exists(mFileName))
            {
                mConnection = new SQLiteConnection(connectionString);
                mConnection.Open();
            }
            else
            {
                Debug.Write("Hello");
            }
        }
        public void SQLiteInsert(Dictionary<string, object> insertDictionary, string tableName)
        {
            string firstPart = string.Format("INSERT INTO {0} ", tableName);
            string keys = "(";
            string values = "(";
            string insString;
            string lastPart;
            for (int i = 0; i < insertDictionary.Count; i++)
            {
                if (i == insertDictionary.Count - 1)
                {
                    keys += insertDictionary.Keys.ElementAt(i).ToString() + ")";
                    values +="\'"+insertDictionary.Values.ElementAt(i).ToString() + "\')";

                }
                else
                {
                    keys += insertDictionary.Keys.ElementAt(i).ToString() + ",";
                    values +="\'"+ insertDictionary.Values.ElementAt(i).ToString() + "\',";

                }
            }
            lastPart = keys + " VALUES " + values;

            insString = firstPart + lastPart;
            SQLiteCommand cmd = new SQLiteCommand(insString, mConnection);
            cmd.ExecuteNonQuery();


        }
        public DataTable SQLiteSelect(string selectQuery)
        {
            SQLiteCommand cmd = new SQLiteCommand(selectQuery, mConnection);
            DataTable tbl = new DataTable();
            SQLiteDataReader reader = cmd.ExecuteReader();
            tbl.Load(reader);
            return tbl;
        }
        public void SQLiteCreateTable(string createQuery)
        {
            SQLiteCommand cmd = new SQLiteCommand(createQuery, mConnection);
            cmd.ExecuteNonQuery();

        }
        public void ExecuteCommand(string query)
        {
            SQLiteCommand cmd = new SQLiteCommand(query, mConnection);
            cmd.ExecuteNonQuery();
        }
        public void SQLiteUpdate(Dictionary<string, object> updateDictionary, Dictionary<string, string> termsDictionary, string tableName)
        {
            string firstPart = string.Format("UPDATE {0} ", tableName);
            string lastPart = "SET ";
            string conditionPart="WHERE ";
            for (int i = 0; i < updateDictionary.Count; i++)
            {
                if (i == updateDictionary.Count - 1)
                {
                    lastPart += updateDictionary.Keys.ElementAt<string>(i).ToString() + "=\'" + updateDictionary.Values.ElementAt(i).ToString() + "\' ";
                }
                else
                {
                    lastPart += updateDictionary.Keys.ElementAt<string>(i).ToString() + "=\'" + updateDictionary.Values.ElementAt(i).ToString() + "\',";
                }

            }
            for (int i = 0; i < termsDictionary.Count; i++)
            {
                if (i == termsDictionary.Count - 1)
                {
                    conditionPart += termsDictionary.Keys.ElementAt<string>(i).ToString() + "=\'" + termsDictionary.Values.ElementAt<string>(i).ToString()+"\'";
                }
                else
                {
                    conditionPart += termsDictionary.Keys.ElementAt<string>(i).ToString() + "=\'" + termsDictionary.Values.ElementAt<string>(i).ToString() + "\' AND";
                }

            }
            string final=firstPart+lastPart+conditionPart;
            SQLiteCommand cmd = new SQLiteCommand(final, mConnection);
            cmd.ExecuteNonQuery();



        }
        public void SQLiteDelete(Dictionary<string, string> termsDictionary, string tableName)
        {
            string firstpart=string.Format("DELETE FROM {0} ",tableName);
            if (termsDictionary.Count != 0)
            {
                firstpart += "WHERE ";
            }
            for(int i=0;i<termsDictionary.Count;i++)
            {
                if (i == termsDictionary.Count - 1)
                {
                    firstpart += termsDictionary.Keys.ElementAt(i).ToString() + "=\'" + termsDictionary.Values.ElementAt(i).ToString()+"\';";

                }
                else
                {
                    firstpart += termsDictionary.Keys.ElementAt(i).ToString() + "=\'" + termsDictionary.Values.ElementAt(i).ToString() + "\' AND ";
                }
                
            }
            SQLiteCommand cmd = new SQLiteCommand(firstpart, mConnection);
            cmd.ExecuteNonQuery();
        }
        public void SQLiteCloseConnection()
        {
            mConnection.Close();
        }
    }
}
